﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace csv2json.convert.objConverter
{
    public class BaseConverter
    {
        //private Dictionary<String, String> relatedPath = new Dictionary<String, String>();
        public Dictionary<String, String> RelatedPath = new Dictionary<String, String>();

        string connstring;

        protected String root;

        protected String mainPath;

        public BaseConverter(String path)
        {
            mainPath = path;

            //Connection String
            connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + mainPath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";  
        }

        public String convert()
        {
            String finalResult = "";

            using(OleDbConnection conn = new OleDbConnection(connstring))
            {
                conn.Open();

                //Get All Sheets Name
                DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[]{null, null, null, "Table"});  

                //Get the First Sheet Name
                int count   =   sheetsName.Rows.Count;
                bool found = false;
                string firstSheetName = "";
                for (int i = 0; !found && i < count; ++i)
                {
                    firstSheetName = sheetsName.Rows[i][2].ToString();
                    if (firstSheetName.Equals(root))
                    {
                        found = true;
                    }
                }

                if (!firstSheetName.Equals(""))
                {
                    finalResult = getBucket("root");
                }
            }

            return finalResult;
        }

        protected String getBucket(String bucket, String value = null)
        {
            String result;

            if (RelatedPath.ContainsKey(bucket))
            {
                result = String.Format("\"{0}\":", bucket);
                result += "\n{";
                //Query String 
                string sql = string.Format("SELECT * FROM [{0}]", RelatedPath[bucket]);
                OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
                DataSet mainSet = new DataSet();
                ada.Fill(mainSet);

                bool firstLine = true;
                List<String> buckets = new List<String>();

                // Loop through dataset
                foreach (DataTable table in mainSet.Tables)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        int counter = 0;
                        bool found = (value == null);
                        String rowContent = "";

                        foreach (String item in row.ItemArray)
                        {
                            if (firstLine)
                            {
                                buckets.Add(item);
                            }
                            else if (found)
                            {
                                rowContent += getBucket(buckets[counter], item);
                                if (counter < buckets.Count-1)
                                {
                                    rowContent += ",\n";
                                }
                                counter++;
                            }
                            // read item
                            else if ((counter == 0) && (buckets.Count != 0) && buckets[counter].Equals("ID") && item.Equals(value))
                            {
                                found = true;
                                counter++;
                            }
                            // not found
                            else
                                break;
                        }
                        if (!firstLine)
                        {
                            result += rowContent;
                        }
                        firstLine = false;
                    }
                }
                result += "\n}";
            }
            else
            {
                result = String.Format("\"{0}\": \"{1}\"", bucket, value);
            }

            return result;
        }
    }
}
